
/******************************************************************************
 * Copyright Cambridge Silicon Radio Limited 2014 - 2015.
 ******************************************************************************/

package com.ihomey.linkuphome.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * MeshSQLHelper class contains all the methods to perform database operations like opening connection,
 * closing connection, insert, update, read, delete and other things.
 */
public class MeshSQLHelper extends SQLiteOpenHelper {

    // Logcat tag
    private static final String TAG = "MeshSQLHelper";

    // Database version and name
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "iHomey.db";

    // Database table names
    static final String TABLE_CATEGORIES = "categories";
    static final String TABLE_SETTINGS = "settings";
    static final String TABLE_DEVICES = "devices";
    static final String TABLE_GROUPS = "groups";
    static final String TABLE_LIGHT_STATE = "lightState";
    static final String TABLE_MODELS = "models";

    // Categories Table - Columns names
    public static final String CATEGORIES_COLUMN_ID = "id";
    public static final String CATEGORIES_COLUMN_TYPE = "type";
    public static final String CATEGORIES_COLUMN_ADDED = "added";

    // Settings Table - Columns names
    public static final String SETTINGS_COLUMN_ID = "id";
    public static final String SETTINGS_COLUMN_KEY = "networkKey";
    public static final String SETTINGS_COLUMN_AUTH_REQUIRED = "authRequired";
    public static final String SETTINGS_COLUMN_NEXT_DEVICE_INDEX = "nextDeviceIndex";
    public static final String SETTINGS_COLUMN_NEXT_GROUP_INDEX = "nextGroupIndex";
    public static final String SETTINGS_COLUMN_TTL = "ttl";

    // Devices Table - Columns names
    public static final String DEVICES_COLUMN_ID = "id";
    public static final String DEVICES_COLUMN_HASH = "hash";
    public static final String DEVICES_COLUMN_NAME = "name";
    public static final String DEVICES_COLUMN_TYPE = "type";
    public static final String DEVICES_COLUMN_GROUPS_SUPPORTED = "groupsSupported";
    public static final String DEVICES_COLUMN_MODELSUPPORT_LOW = "modelSupportL";
    public static final String DEVICES_COLUMN_MODELSUPPORT_HIGH = "modelSupportH";

    // Groups Table - Columns names
    public static final String GROUPS_COLUMN_ID = "id";
    public static final String GROUPS_COLUMN_NAME = "name";
    public static final String GROUPS_COLUMN_TYPE = "type";


    // LightState Table - Columns names
    public static final String LIGHT_STATE_COLUMN_DEVICE_ID = "deviceID";
    public static final String LIGHT_STATE_COLUMN_TYPE = "type";
    public static final String LIGHT_STATE_COLUMN_IS_ON = "isOn";
    public static final String LIGHT_STATE_COLUMN_IS_LIGHT = "isLight";
    public static final String LIGHT_STATE_COLUMN_CHANGE_MODE = "changeMode";
    public static final String LIGHT_STATE_COLUMN_COLOR_POSITION = "colorPosition";
    public static final String LIGHT_STATE_COLUMN_BRIGHTNESS = "brightness";
    public static final String LIGHT_STATE_COLUMN_SCENE_MODE = "sceneMode";
    public static final String LIGHT_STATE_COLUMN_OPEN_TIMER = "openTimer";
    public static final String LIGHT_STATE_COLUMN_CLOSE_TIMER = "closeTimer";
    public static final String LIGHT_STATE_COLUMN_IS_ON_OPEN_TIMER = "isOnOpenTimer";
    public static final String LIGHT_STATE_COLUMN_IS_ON_CLOSE_TIMER = "isOnCloseTimer";


    // Models Table - Columns names
    public static final String MODELS_COLUMN_DEVICE_ID = "deviceID";
    public static final String MODELS_COLUMN_GROUP_ID = "groupID";
    public static final String MODELS_COLUMN_TYPE = "type";

    // Categories table create statement
    private static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE " + TABLE_CATEGORIES +
            "(" + CATEGORIES_COLUMN_ID + " INTEGER PRIMARY KEY autoincrement," + CATEGORIES_COLUMN_TYPE + " INTEGER," + CATEGORIES_COLUMN_ADDED + " BOOLEAN" + ")";

    // Settings table create statement
    private static final String CREATE_TABLE_SETTINGS = "CREATE TABLE " + TABLE_SETTINGS +
            "(" + SETTINGS_COLUMN_ID + " INTEGER PRIMARY KEY autoincrement," + SETTINGS_COLUMN_AUTH_REQUIRED + " BOOLEAN," + SETTINGS_COLUMN_KEY + " TEXT," +
            SETTINGS_COLUMN_NEXT_DEVICE_INDEX + " INTEGER," + SETTINGS_COLUMN_NEXT_GROUP_INDEX + " INTEGER," + SETTINGS_COLUMN_TTL + " INTEGER" + ")";

    // Devices table create statement
    private static final String CREATE_TABLE_DEVICES = "CREATE TABLE " + TABLE_DEVICES +
            "(" + DEVICES_COLUMN_ID + " INTEGER," + DEVICES_COLUMN_HASH + " INTEGER," + DEVICES_COLUMN_NAME + " TEXT," + DEVICES_COLUMN_TYPE + " INTEGER," + DEVICES_COLUMN_GROUPS_SUPPORTED + " INTEGER," + DEVICES_COLUMN_MODELSUPPORT_LOW + " INTEGER," + DEVICES_COLUMN_MODELSUPPORT_HIGH + " INTEGER, PRIMARY KEY (" + DEVICES_COLUMN_ID + "," + DEVICES_COLUMN_TYPE + ")" + ")";


    // Models table create statement
    private static final String CREATE_TABLE_MODELS = "CREATE TABLE " + TABLE_MODELS +
            "(" + MODELS_COLUMN_DEVICE_ID + " INTEGER NOT NULL," + MODELS_COLUMN_GROUP_ID + " INTEGER NOT NULL," + MODELS_COLUMN_TYPE + " INTEGER," + " PRIMARY KEY (" + MODELS_COLUMN_DEVICE_ID + "," + MODELS_COLUMN_GROUP_ID + "," + MODELS_COLUMN_TYPE + ")" + ")";

    // Groups table create statement
    private static final String CREATE_TABLE_GROUPS = "CREATE TABLE " + TABLE_GROUPS +
            "(" + GROUPS_COLUMN_ID + " INTEGER," + GROUPS_COLUMN_NAME + " TEXT,"  + GROUPS_COLUMN_TYPE + " INTEGER," + " PRIMARY KEY (" + GROUPS_COLUMN_ID + "," + GROUPS_COLUMN_TYPE + ")" + ")";


    // LightState table create statement
    private static final String CREATE_TABLE_LIGHT_STATE = "CREATE TABLE " + TABLE_LIGHT_STATE +
            "(" + LIGHT_STATE_COLUMN_DEVICE_ID + " INTEGER NOT NULL," + LIGHT_STATE_COLUMN_TYPE + " INTEGER," + LIGHT_STATE_COLUMN_IS_ON + " INTEGER," + LIGHT_STATE_COLUMN_IS_LIGHT + " INTEGER," + LIGHT_STATE_COLUMN_CHANGE_MODE + " INTEGER," + LIGHT_STATE_COLUMN_BRIGHTNESS + " INTEGER," + LIGHT_STATE_COLUMN_COLOR_POSITION + " INTEGER," + LIGHT_STATE_COLUMN_SCENE_MODE + " INTEGER," + LIGHT_STATE_COLUMN_OPEN_TIMER + " INTEGER," + LIGHT_STATE_COLUMN_CLOSE_TIMER + " INTEGER," + LIGHT_STATE_COLUMN_IS_ON_OPEN_TIMER + " INTEGER," + LIGHT_STATE_COLUMN_IS_ON_CLOSE_TIMER + " INTEGER," + " PRIMARY KEY (" + LIGHT_STATE_COLUMN_DEVICE_ID + "," + LIGHT_STATE_COLUMN_TYPE + ")" + ")";

    public MeshSQLHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database) {

        Log.w(TAG, "Creating database...");
        // creating required tables
        database.execSQL(CREATE_TABLE_CATEGORIES);
        database.execSQL(CREATE_TABLE_SETTINGS);
        database.execSQL(CREATE_TABLE_DEVICES);
        database.execSQL(CREATE_TABLE_MODELS);
        database.execSQL(CREATE_TABLE_GROUPS);
        database.execSQL(CREATE_TABLE_LIGHT_STATE);

        initData(database);
    }

    private void initData(SQLiteDatabase database) {
        database.execSQL("insert into categories(type,added) Values(1,0)");
        database.execSQL("insert into categories(type,added) Values(2,0)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");

        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SETTINGS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_DEVICES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MODELS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_GROUPS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_LIGHT_STATE);

        // create new tables
        onCreate(db);
    }

}
